﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class ExpenseDAL : IExpenseDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public ExpenseDAL()
        {

        }

        public IList<TreeViewInfo> TreeView(IList<TreeViewInfo> treeView, bool isRootNode, ExpenseInfo expInfo)
        {
            string strSQL;
            DataTable dtb;
            List<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@expGrp", expInfo.ExpenseGroup.Sequence);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "select * from bgm_mas_expense where recoed_status not like @status"
                    + " order by bgm_exp_code";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                return infoList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Insert(ExpenseInfo expInfo)
        {
            string strSQL;
            Nullable<int> parent = null;
            int nResult = 0;

            if (expInfo.Parent != null)
            {
                if(expInfo.Parent.Sequence > 0)
                    parent = expInfo.Parent.Sequence;
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@code", expInfo.Code);
                dbManager.AddParameters(1, "@name", expInfo.Name);
                dbManager.AddParameters(2, "@descr", expInfo.Description);
                dbManager.AddParameters(3, "@expParent", parent);
                dbManager.AddParameters(4, "@expGrp", expInfo.ExpenseGroup.Sequence);
                dbManager.AddParameters(5, "@status", SystemInfo._ActiveRecordStatus);

                dbManager.AddParameters(6, "@icm_resource_type", expInfo.Icm_resource_type);
                dbManager.AddParameters(7, "@icm_resource_seq", expInfo.Icm_resource_seq);
                dbManager.AddParameters(8, "@gfmis_code", expInfo.Gfmis_code);
                dbManager.AddParameters(9, "@expGrpReal", expInfo.ExpenseGroupReal.Sequence);

                strSQL = "INSERT INTO BGM_MAS_EXPENSE (BGM_EXP_CODE, BGM_EXP_NAME"
                        + ", BGM_EXP_DESCR, UPPER_BGM_EXP_SEQ, BGM_EXP_GRP_SEQ, RECORD_STATUS"
                        + ", ICM_RESOURCE_TYPE, ICM_RESOURCE_SEQ, GFMIS_CODE, bgm_exp_grp_id_real)"
                    + " VALUES (@code, @name, @descr, @expParent, @expGrp, @status"
                        + ", @icm_resource_type, @icm_resource_seq, @gfmis_code, @expGrpReal);"
                    + " SELECT scope_identity()";
                string strLastSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                dbManager.CommitTransaction();

                //strSQL = "insert into bgm_mas_expense_source"
                //    + " (bgm_exp_seq, bgm_source_seq)"
                //    + " values (" + strLastSeq + ", 1)";
                //dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                //strSQL = "insert into bgm_mas_expense_source"
                //    + " (bgm_exp_seq, bgm_source_seq)"
                //    + " values (" + strLastSeq + ", 6)";
                //dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                //strSQL = "insert into bgm_mas_expense_source"
                //    + " (bgm_exp_seq, bgm_source_seq)"
                //    + " values (" + strLastSeq + ", 7)";
                //dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                //---------------------------------------

                return Convert.ToInt32(strLastSeq);
            }
            catch
            {
                dbManager.RollBackTransaction();
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(ExpenseInfo expInfo)
        {
            string strSQL;

            //Nullable<int> parent = null;
            //if (expInfo.Parent.Sequence > 0)
            //    parent = expInfo.Parent.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@seq", expInfo.Sequence);
                dbManager.AddParameters(1, "@code", expInfo.Code);
                dbManager.AddParameters(2, "@name", expInfo.Name);
                dbManager.AddParameters(3, "@descr", expInfo.Description);
                //dbManager.AddParameters(4, "@expParent", parent);
                //dbManager.AddParameters(5, "@expGrp", expInfo.ExpenseGroup.Sequence);
                dbManager.AddParameters(4, "@status", SystemInfo._ActiveRecordStatus);

                dbManager.AddParameters(5, "@icm_resource_type", expInfo.Icm_resource_type);
                dbManager.AddParameters(6, "@icm_resource_seq", expInfo.Icm_resource_seq);
                dbManager.AddParameters(7, "@gfmis_code", expInfo.Gfmis_code);
                dbManager.AddParameters(8, "@expGrpReal", expInfo.ExpenseGroupReal.Sequence);

                strSQL = "UPDATE BGM_MAS_EXPENSE"
                + " SET BGM_EXP_CODE = @code"
                    + ", BGM_EXP_NAME = @name"
                    + ", BGM_EXP_DESCR = @descr"
                    //+ ", UPPER_BGM_EXP_SEQ = @expParent"
                    + ", bgm_exp_grp_id_real = @expGrpReal"
                    + ", RECORD_STATUS = @status"
                    + ", ICM_RESOURCE_TYPE = @icm_resource_type"
                    + ", ICM_RESOURCE_SEQ = @icm_resource_seq"
                    + ", GFMIS_CODE = @gfmis_code"
                + " WHERE bgm_exp_seq = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(int seq)
        {
            //string strSQL;

            try
            {
                //dbManager.Open();
                //dbManager.CreateParameters(2);

                //dbManager.AddParameters(0, "@seq", seq);
                //dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                //strSQL = "UPDATE BGM_MAS_EXPENSE SET RECORD_STATUS = @status WHERE BGM_EXP_SEQ = @seq";

                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                DeleteRecuresive(seq);
            }
            catch
            {
                //return 0;
            }
            finally
            {
                //dbManager.CloseReader();
                //dbManager.Dispose();
            }
        }

        private void DeleteRecuresive(int seq)
        {
            string strSQL;
            DataTable dtb;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "UPDATE BGM_MAS_EXPENSE SET RECORD_STATUS = @status WHERE BGM_EXP_SEQ = @seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "select bgm_exp_seq"
                    + " from bgm_mas_expense"
                    + " where upper_bgm_exp_seq = @seq";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    DeleteRecuresive(Convert.ToInt32(dr[0]));
                }
            }
            catch
            {
                
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<ExpenseInfo> Search(ExpenseInfo expInfo)
        {
            string strSQL;
            Nullable<int> parent = null;
            int expGrp = 0;
            int expGrpReal = 0;

            if (expInfo.Parent != null)
                parent = expInfo.Parent.Sequence;

            if (expInfo.ExpenseGroup != null)
                expGrp = expInfo.ExpenseGroup.Sequence;

            if (expInfo.ExpenseGroupReal != null)
                expGrpReal = expInfo.ExpenseGroupReal.Sequence;

            List<ExpenseInfo> expList = new List<ExpenseInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@seq", expInfo.Sequence);
                dbManager.AddParameters(1, "@code", expInfo.Code);
                dbManager.AddParameters(2, "@name", expInfo.Name);
                dbManager.AddParameters(3, "@descr", expInfo.Description);
                dbManager.AddParameters(4, "@parent", parent);
                dbManager.AddParameters(5, "@expGrp", expGrp);
                dbManager.AddParameters(6, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(7, "@expGrpReal", expGrpReal);

                strSQL = "SELECT e.BGM_EXP_SEQ, e.BGM_EXP_CODE, e.BGM_EXP_NAME, e.BGM_EXP_DESCR"
                    + ", e.RECORD_STATUS, e.UPPER_BGM_EXP_SEQ, e.BGM_EXP_GRP_SEQ"
                    + ", e.ICM_RESOURCE_TYPE, e.ICM_RESOURCE_SEQ, e.GFMIS_CODE, e.bgm_exp_grp_id_real"
                    + " FROM BGM_MAS_EXPENSE e"
                    + " WHERE e.RECORD_STATUS not like @status"
                    + ((expInfo.Sequence > 0) ? " AND e.BGM_EXP_SEQ = @seq" : "")
                    + ((expInfo.Code.Length > 0) ? " AND e.BGM_EXP_CODE = @code" : "")
                    + ((expInfo.Name.Length > 0) ? " AND e.BGM_EXP_NAME = @name" : "")
                    + ((expInfo.Description.Length > 0) ? " AND e.BGM_EXP_DESCR = @descr" : "")
                    + ((parent == null) ? " AND e.UPPER_BGM_EXP_SEQ IS NULL" : "")
                    + ((parent > 0) ? " AND e.UPPER_BGM_EXP_SEQ = @parent" : "")
                    + ((expGrp > 0) ? " AND e.BGM_EXP_GRP_SEQ = @expGrp" : "")
                    + ((expGrpReal > 0) ? " AND e.bgm_exp_grp_id_real = @expGrpReal" : "")
                + " ORDER BY e.BGM_EXP_CODE";

                DataTable drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                foreach(DataRow dr in drc.Rows)
                {
                    ExpenseInfo expParentInfo = new ExpenseInfo();

                    if (dr["UPPER_BGM_EXP_SEQ"] == DBNull.Value)
                        expParentInfo = null;
                    else expParentInfo.Sequence = Convert.ToInt32(dr["UPPER_BGM_EXP_SEQ"]);

                    ExpenseGroupDAL expGrpCtrl = new ExpenseGroupDAL();

                    IList<ExpenseGroupInfo> expGrpInfo = expGrpCtrl.Search(new ExpenseGroupInfo(Convert.ToInt32(dr["BGM_EXP_GRP_SEQ"]), "", "", "", "", null, null, null, null));
                    IList<ExpenseGroupInfo> expGrpRealInfo = expGrpCtrl.Search(new ExpenseGroupInfo(Convert.ToInt32(dr["bgm_exp_grp_id_real"]), "", "", "", "", null, null, null, null));

                    ExpenseInfo exp = new ExpenseInfo();

                    exp.Sequence = Convert.ToInt32(dr["BGM_EXP_SEQ"]);
                    exp.Code = Convert.ToString(dr["BGM_EXP_CODE"]);
                    exp.Name = Convert.ToString(dr["BGM_EXP_NAME"]);
                    exp.Description = Convert.ToString(dr["BGM_EXP_DESCR"]);
                    exp.Parent = expParentInfo;
                    exp.Icm_resource_type = dr["ICM_RESOURCE_TYPE"].ToString();
                    exp.Icm_resource_seq = cn.ConvertNullInt(dr["ICM_RESOURCE_SEQ"]);
                    exp.Gfmis_code = dr["GFMIS_CODE"].ToString();
                    exp.ExpenseGroup = expGrpInfo != null && expGrpInfo.Count > 0 ? expGrpInfo[0] : null;
                    exp.ExpenseGroupReal = expGrpRealInfo != null && expGrpRealInfo.Count > 0 ? expGrpRealInfo[0] : null;

                    expList.Add(exp);
                }

                //while (db.Read())
                //{
                //    ExpenseInfo expParentInfo = new ExpenseInfo();

                //    if (db.IsDBNull(5))
                //        expParentInfo = null;
                //    else expParentInfo.Sequence = db.GetInt32(5);

                //    ExpenseGroupDAL expGrpCtrl = new ExpenseGroupDAL();

                //    IList<ExpenseGroupInfo> expGrpInfo = expGrpCtrl.Search(new ExpenseGroupInfo(rdr.GetInt32(6), "", "", "", "", null, null, null, null));
                //    IList<ExpenseGroupInfo> expGrpRealInfo = expGrpCtrl.Search(new ExpenseGroupInfo(rdr.GetInt32(11), "", "", "", "", null, null, null, null));

                //    ExpenseInfo exp = new ExpenseInfo(rdr.GetInt32(0)
                //        , (rdr.IsDBNull(1)) ? "" : rdr.GetString(1)
                //        , (rdr.IsDBNull(2)) ? "" : rdr.GetString(2)
                //        , (rdr.IsDBNull(3)) ? "" : rdr.GetString(3)
                //        , rdr.GetString(4), expParentInfo, expGrpInfo[0], expGrpRealInfo[0], null
                //        , (rdr.IsDBNull(8)) ? "" : rdr.GetString(8)
                //        , (rdr.IsDBNull(9)) ? (int?)null : rdr.GetInt32(9)
                //        , (rdr.IsDBNull(10)) ? "" : rdr.GetString(10)
                //        );

                //    expList.Add(exp);
                //}

                return expList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public bool CheckDuplicateCode(string code)
        {
            string strSQL;
            bool result = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@deletestatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@BGM_EXP_CODE", code);

                strSQL = @"SELECT BGM_EXP_CODE from bgm_mas_expense
                          WHERE BGM_EXP_CODE = @BGM_EXP_CODE
                          AND record_status not like @deletestatus";
                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true; // found duplicate
                    break;
                }

            }
            catch (Exception error)
            { }
            finally 
            {
                dbManager.CloseReader();
                dbManager.Dispose();
             }

                return result;
            }
        }
    }

